package com.mathlearning.auth.repository;

import com.mathlearning.auth.entity.Problem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface ProblemRepository extends JpaRepository<Problem, Long>, JpaSpecificationExecutor<Problem> {
    
    /**
     * 统计课程下的题目数量
     */
    long countByCourseId(Long courseId);
    
    /**
     * 统计章节下的题目数量
     */
    long countByChapterId(Long chapterId);
    
    /**
     * 根据课程ID分页查询（排除已删除）
     */
    Page<Problem> findByCourseIdAndDeleted(Long courseId, Integer deleted, Pageable pageable);
    
    /**
     * 根据章节ID分页查询（排除已删除）
     */
    Page<Problem> findByChapterIdAndDeleted(Long chapterId, Integer deleted, Pageable pageable);
    
    /**
     * 根据课程ID和难度分页查询（排除已删除）
     */
    Page<Problem> findByCourseIdAndDifficultyAndDeleted(Long courseId, String difficulty, Integer deleted, Pageable pageable);
    
    /**
     * 查询所有未删除的题目
     */
    Page<Problem> findByDeleted(Integer deleted, Pageable pageable);
    
    /**
     * 统计未删除的课程题目数量
     */
    long countByCourseIdAndDeleted(Long courseId, Integer deleted);
    
    /**
     * 统计未删除的章节题目数量
     */
    long countByChapterIdAndDeleted(Long chapterId, Integer deleted);
    
    /**
     * 随机获取一道题目（完全随机）
     */
    @Query(value = "SELECT * FROM problem WHERE deleted = 0 ORDER BY RAND() LIMIT 1", 
           nativeQuery = true)
    java.util.Optional<Problem> findRandomProblem();
    
    /**
     * 随机获取一道题目（排除用户已做的题目）
     */
    @Query(value = "SELECT * FROM problem " +
                   "WHERE deleted = 0 " +
                   "AND id NOT IN " +
                   "(SELECT DISTINCT problem_id FROM user_submission WHERE user_id = :userId) " +
                   "ORDER BY RAND() LIMIT 1", 
           nativeQuery = true)
    java.util.Optional<Problem> findRandomProblemExcludingDone(@Param("userId") Long userId);
    
    /**
     * 按难度随机获取一道题目
     */
    @Query(value = "SELECT * FROM problem " +
                   "WHERE deleted = 0 AND difficulty = :difficulty " +
                   "ORDER BY RAND() LIMIT 1", 
           nativeQuery = true)
    java.util.Optional<Problem> findRandomProblemByDifficulty(@Param("difficulty") String difficulty);
    
    /**
     * 按难度随机获取一道题目（排除已做的）
     */
    @Query(value = "SELECT * FROM problem " +
                   "WHERE deleted = 0 " +
                   "AND difficulty = :difficulty " +
                   "AND id NOT IN " +
                   "(SELECT DISTINCT problem_id FROM user_submission WHERE user_id = :userId) " +
                   "ORDER BY RAND() LIMIT 1", 
           nativeQuery = true)
    java.util.Optional<Problem> findRandomProblemByDifficultyExcludingDone(
        @Param("difficulty") String difficulty, 
        @Param("userId") Long userId
    );
    
    /**
     * 查询所有未删除的题目ID（用于题目池）
     */
    @Query(value = "SELECT id FROM problem WHERE deleted = 0 ORDER BY id", 
           nativeQuery = true)
    java.util.List<Long> findAllActiveProblemIds();
}




